from src.main.server.mapper.CreateMysqlCon import CreateMysqlCon


class ScoreImpl:
    def __init__(self):
        self.con = CreateMysqlCon().get_con()
        if not self.con:
            raise ConnectionError("数据库连接失败，请检查配置或网络")
        self.cursor = self.con.cursor()

    def all_info(self):
        SQL = ("select v.username, sp.server_name, s.get_score "
               "from score as s,volunteers_info as v, server_project as sp "
               "where s.vol_id = v.ID and s.ser_name_id = sp.ID")

        self.cursor.execute(SQL)
        return self.cursor.fetchall()

    def update(self, data):
        SQL = ("""
               update score s
                   JOIN server_project sp ON s.ser_name_id = sp.ID
                   JOIN volunteers_info v ON s.vol_id = v.ID
               set s.get_score = %s
               where v.username = %s
                 and sp.server_name = %s
               """)
        self.cursor.execute(SQL, data)

    def get_info(self, data):
        SQL = ("""
               SELECT v.username     AS username,
                      sp.server_name AS server_name,
                      s.get_score    AS score
               FROM score s
                        JOIN server_project sp ON s.ser_name_id = sp.ID
                        JOIN volunteers_info v ON s.vol_id = v.ID
               WHERE v.username = %s
                 AND sp.server_name = %s
               """)
        self.cursor.execute(SQL, data)
        return self.cursor.fetchall()

    def check(self, data):
        """确认数据库是否已经存在该数据
                   保证唯一性
                """
        SQL = """
              SELECT count(*)
              FROM score s
                       JOIN server_project sp ON s.ser_name_id = sp.ID
                       JOIN volunteers_info v ON s.vol_id = v.ID
              WHERE v.username = %s
                AND sp.server_name = %s \
              """
        self.cursor.execute(SQL, data)
        return self.cursor.fetchall()

    def insert(self, data):
        SQL = ("""
               INSERT INTO score (ser_name_id, vol_id, get_score)
               SELECT sp.ID, v.ID, %s
               FROM server_project sp
                        JOIN volunteers_info v ON v.username = %s AND sp.server_name = %s
               """)
        self.cursor.execute(SQL, data)


if __name__ == '__main__':
    print(ScoreImpl().get_info(("李四", "社区服务")))